﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace ExcelImport
{
    /// <summary>
    /// Excel 导入助手
    /// 
    /// 不支持集合导出
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class ExcelImportHelper<T> : BaseExcelImport<T> where T : ExcelData, new()
    {
        //--------------------Members--------------------

        #region 成员属性

        /// <summary>
        /// (模板)列头文本信息
        /// </summary>
        protected List<string> _headerTexts
        {
            get => _dataFields.Keys.ToList();
        }

        /// <summary>
        /// 斜杠
        /// </summary>
        private readonly char _slash = '/';

        /// <summary>
        /// 反斜杠
        /// </summary>
        private readonly char _backSlash = '\\';

        /// <summary>
        /// 分隔符
        /// </summary>
        private char _separator
        {
            get
            {
                return _backSlash;
            }
        }

        /// <summary>
        /// Excel 数据属性字典
        /// </summary>
        protected Dictionary<string, string> _dataFields { get; private set; }

        #endregion


        //--------------------Methods--------------------

        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelImportHelper()
        {
            _dataFields = new Dictionary<string, string>();
            var properties = typeof(T).GetProperties();
            foreach (var prop in properties)
            {
                var attribute = Attribute.GetCustomAttribute(prop, typeof(ExcelHeaderAttribute));
                if (attribute != null)
                {
                    string name = (attribute as ExcelHeaderAttribute).Header;
                    _dataFields.Add(name, prop.Name);
                }
            }
        }

        #endregion

        /// <summary>
        /// 初始化工作薄
        /// </summary>
        private void InitWorkbook(bool isTemplate)
        {
            _workbook = new HSSFWorkbook();
            //创建表
            _sheet = _workbook.CreateSheet(SheetName);

            //创建第一行并填写数据
            _row = _sheet.CreateRow(0);
            int headerCount;
            if (isTemplate)
            {
                SetRowTexts(_row, 0, _headerTexts.ToArray());
                headerCount = _headerTexts.Count;
            }
            else
            {
                SetRowTexts(_row, 0, _dataFields.Keys.ToArray());
                headerCount = _dataFields.Count;
            }

            //设置列头样式
            ICellStyle cellStyle = GetCellStyle(_workbook, "Header");
            for (int i = 0; i < headerCount; i++)
            {
                ICell cell = _row.GetCell(i);
                _row.GetCell(i).CellStyle = cellStyle;
            }

            SetRowComment(_sheet, _row);

            SetDataValidation(_sheet, _row);
        }

        /// <summary>
        /// 填写数据
        /// </summary>
        /// <param name="datas"></param>
        protected virtual void FillWorkbook(IEnumerable<T> datas)
        {
            int index = 0;
            foreach (var data in datas)
            {
                List<string> fields = new List<string>();
                foreach (var field in typeof(T).GetProperties())
                {
                    object value = field.GetValue(data, null);
                    if (value == null)
                    {
                        value = string.Empty;
                    }
                    fields.Add(value.ToString());
                }
                _row = _sheet.CreateRow(++index);
                SetRowTexts(_row, 0, fields.ToArray());
            }
        }

        /// <summary>
        /// 填写行文本信息
        /// </summary>
        /// <param name="row">行对象</param>
        /// <param name="col">开始列</param>
        /// <param name="texts">文本信息集合</param>
        private void SetRowTexts(IRow row, int col, string[] texts)
        {
            foreach (string text in texts)
            {
                row.CreateCell(col).SetCellValue(text);
                col++;
            }
        }

        /// <summary>
        /// 获取文本所在列索引
        /// </summary>
        /// <param name="row">行对象</param>
        /// <param name="text">文本信息</param>
        /// <param name="index">列索引</param>
        /// <returns></returns>
        private bool TryGetColumnIndexInRowByText(IRow row, string text, ref int index)
        {
            bool result = false;
            foreach (ICell cell in row.Cells)
            {
                if (cell.StringCellValue == text)
                {
                    index = cell.ColumnIndex;
                    result = true;
                    break;
                }
            }
            return result;
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private IEnumerable<T> GetDatasFromSheet(ISheet sheet)
        {
            List<T> result = new List<T>();
            //获取列头文本与列索引关系
            IRow row = sheet.GetRow(0);

            if (row == null)
                return null;

            Dictionary<string, int> propertyDic = GeneratePropertyDic(row);

            //依次填充结构体
            for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
            {
                row = sheet.GetRow(i);
                if (row == null)
                    continue;

                T data = new T();
                if (TryFillData(row, propertyDic, ref data))
                {
                    result.Add(data);
                }
            }
            return result;
        }

        /// <summary>
        /// 生成属性字典
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        protected Dictionary<string, int> GeneratePropertyDic(IRow row)
        {
            Dictionary<string, int> result = new Dictionary<string, int>();
            foreach (var cell in row.Cells)
            {
                if (!result.ContainsKey(cell.StringCellValue))
                {
                    result.Add(cell.StringCellValue, cell.ColumnIndex);
                }
            }
            return result;
        }

        /// <summary>
        /// 填充IPCameraData结构体
        /// </summary>
        /// <param name="row">行对象</param>
        /// <param name="headerDic">列头字典</param>
        /// <param name="data">IPCameraData结构体</param>
        /// <returns>是否填充</returns>
        protected bool TryFillData(IRow row, Dictionary<string, int> headerDic, ref T data)
        {
            //导入逻辑右边，设备名称空当作子码流，故不做判断
            //判断设备名称是否为空
            //if (string.IsNullOrWhiteSpace(CellValueConvert2String(row.GetCell(headerDic["设备名称"], MissingCellPolicy.CREATE_NULL_AS_BLANK))))
            //    return false;

            //进行分类填充
            foreach (var item in headerDic)
            {
                object value = CellValueConvert2String(row.GetCell(item.Value, MissingCellPolicy.CREATE_NULL_AS_BLANK));
                if (_dataFields.ContainsKey(item.Key))
                {
                    var prop = typeof(T).GetProperty(_dataFields[item.Key]);
                    if (prop != null)
                    {
                        try
                        {
                            prop.SetValue(data, value, null);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine($"ExcelImportHelper.TryFillData error. {ex.Message}");
                        }
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="path">指定的文件路径</param>
        /// <param name="datas">指定要导出的数据</param>
        /// <returns></returns>
        public override bool ExportData(string path, IEnumerable<T> datas)
        {
            if (!CheckExportFilePath(path))
            {
                return false;
            }
            using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write))
            {
                InitWorkbook(false);
                FillWorkbook(datas);

                //将数据写入至 Excel 文件
                _workbook.Write(fs);
            }

            return true;
        }

        /// <summary>
        /// 导出模板
        /// </summary>
        /// <param name="path">指定模板的路径</param>
        /// <returns></returns>
        public override bool ExportTemplate(string path)
        {
            if (!CheckExportFilePath(path))
            {
                return false;
            }
            using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write))
            {
                InitWorkbook(true);

                //将数据写入至 Excel 文件
                _workbook.Write(fs);
            }

            return true;
        }

        /// <summary>
        /// 导入指定文件的路径
        /// </summary>
        /// <param name="path">指定路径</param>
        /// <returns></returns>
        public override IEnumerable<T> Import(string path)
        {
            List<T> result = new List<T>();
            bool xlsxFile = string.Equals(Path.GetExtension(path).ToLower(), ".xlsx");
            using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                _workbook = GetWorkbookBySuffix(fs, xlsxFile);
                for (int sheetIndex = 0; sheetIndex < _workbook.NumberOfSheets; sheetIndex++)
                {
                    if (_workbook.GetSheetName(sheetIndex).Contains(SheetName))
                    {
                        _sheet = _workbook.GetSheetAt(sheetIndex);
                        var datas = GetDatasFromSheet(_sheet);
                        if (datas != null)
                        {
                            result.AddRange(GetDatasFromSheet(_sheet));
                        }
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 设置列头备注
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="row">行号</param>
        protected void SetRowComment(ISheet sheet, IRow row)
        {
            List<string> comments = new List<string>();

            foreach (var prop in typeof(T).GetProperties())
            {
                var attr = prop.GetCustomAttributes(typeof(ExcelHeaderAttribute), false).FirstOrDefault() as ExcelHeaderAttribute;
                comments.Add(attr.Comment);
            }

            for (int i = 0; i < comments.Count; i++)
            {
                if (!string.IsNullOrWhiteSpace(comments[i]))
                {
                    row.GetCell(i).CellComment = GetCellComment(sheet, 5, 8, comments[i], "someone");
                }
            }
        }

        /// <summary>
        /// 设置数据有效性
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="row"></param>
        protected void SetDataValidation(ISheet sheet, IRow row)
        {
            List<string[]> validationDatas = new List<string[]>();

            foreach (var prop in typeof(T).GetProperties())
            {
                var attr = prop.GetCustomAttributes(typeof(ExcelHeaderAttribute), false).FirstOrDefault() as ExcelHeaderAttribute;
                validationDatas.Add(attr.ValidationData);
            }

            for (int i = 0; i < validationDatas.Count; i++)
            {
                if (validationDatas[i] != null && validationDatas[i].Length > 0)
                {
                    SetDataValidation(sheet, 1, 10000, row.GetCell(i).ColumnIndex, row.GetCell(i).ColumnIndex, validationDatas[i]);
                }
            }
        }
    }
}
